package infoserver;

import java.sql.*;
import java.sql.Driver;

/**
 * Hier wordt de verbinding met de database gemaakt en de sql querys afgehandeld
 * @author Michael Stevens
 * @since 03-05-2012
 */
public class SqlConnection {    
    private Connection connection;
    private Statement statement;
    private Infoserver is;
    private ResultSet rs;
    
    
    
    public SqlConnection(Infoserver is){
        this.is = is;
    }
    
    /**
     * Maakt de verbinding met de externe MSSQL database
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public void connect(){
        try{
            try{
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            }catch(Exception e){
                System.out.println(e.getMessage());
                is.getGui().writeLog("Driver niet gevonden");
            }
            
            connection = DriverManager.getConnection("jdbc:sqlserver://" + is.getGui().getFieldIp() + ":" + is.getGui().getFieldPort() + ";" + 
                     "databaseName=" + is.getGui().getFieldDbname() + ";" + "user=" + is.getGui().getFieldUser() + ";" + "password=" + is.getGui().getFieldPass());
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);            
        }catch(Exception e){            
            is.getGui().writeLog("Verbinding met MSSQLserver mislukt " + e.getMessage());
        }
    }
    
    
    /**
     * Kijkt of de verbinding met de database actief is
     * @return the connection state
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public boolean isConnected(){
        boolean connected = false;
        try{
            if(connection == null){
                connected = false;                
            }else if(connection.isValid(0)){
                connected = true; 
                is.getGui().writeLog("Verbinding met MSSQL server gelukt");
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        return connected;
    }
    
    /**
     * Sluit de verbinding en de statement als deze nog actief is.
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public void closeConnection(){
        try{
            if(connection.isValid(0)){
                connection.close();
                statement.close();
            }
            is.getGui().writeLog("Verbinding met MSSQL verbroken");
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
    }
    
    /**
     * Voer select query uit en geef de resultaten terug
     * @returns results
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public ResultSet excecuteSelectQuery(String query){        
        try{
            rs = statement.executeQuery(query);           
        }catch(Exception e){
            System.out.println(e.getMessage());
        }        
        return rs;        
    }
    
    /**
     * Voer overige query uit en geef terug of het gelukt is of niet
     * @returns results
     * @author Michael Stevens
     * @since 03-05-2011
     */
    public int excecuteUpdateQuery(String query){
        int gelukt = 0;
        try{
            gelukt = statement.executeUpdate(query);
        }catch(Exception e){
            System.out.println(e.getMessage());
            return 0;
        }finally{
            return gelukt;
        }       
        
    }
    
}
